/*
 * DB.java
 *
 * Created on March 27, 2007, 1:08 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package org.atomojo.app.db;

import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import java.net.URI;
import java.net.URL;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.UUID;
import java.util.logging.Logger;
import org.atomojo.app.AtomResource;
import org.atomojo.app.Nil;
import org.atomojo.app.Storage;
import org.atomojo.app.admin.AdminXML;
import org.atomojo.app.auth.AuthException;
import org.atomojo.app.auth.AuthService;
import org.atomojo.app.auth.User;
import org.atomojo.app.client.XML;
import org.infoset.xml.Attribute;
import org.infoset.xml.Document;
import org.infoset.xml.DocumentLoader;
import org.infoset.xml.Element;
import org.infoset.xml.InfosetFactory;
import org.infoset.xml.ItemConstructor;
import org.infoset.xml.ItemDestination;
import org.infoset.xml.Name;
import org.infoset.xml.XMLException;
import org.infoset.xml.sax.SAXDocumentLoader;
import org.infoset.xml.util.XMLWriter;
import org.restlet.data.MediaType;

/**
 *
 * @author alex
 */
public class DB extends DBConnectionPool {
   
   static final String [] DBDriver_Prop = { "atomojo.db.driver", "org.apache.derby.jdbc.EmbeddedDriver" } ;
   static final String [] DBLocation_Prop = { "atomojo.db.location", "." } ;
   static final String CONF_SYNC_LOCAL = "/conf/sync/local";
   static final String CONF_SYNC_REMOTE = "/conf/sync/remote";
   static final String CONF_SYNC_PROCESS = "/conf/sync/process";
   
   public interface MediaEntryListener {
      void onDelete(EntryMedia resource);
   }
   
   static String getProperty(String [] spec) {
      String value = System.getProperty(spec[0]);
      if (value==null) {
         value = spec[1];
      }
      return value;
   }
   
   public static String getSafeString(String value) {
      return value.replace("'","''");
   }
   
   class FeedIterator extends DBIterator<Feed> {
      FeedIterator(ResultSet set,DBConnectionPool pool,DBConnection connection)
      {
         super(set,new ResultConstructor<Feed>() {
            public Feed newInstance(ResultSet set) 
               throws SQLException
            {
               int id = set.getInt(1);
               return getFeed(id);
            }
         },pool,connection);
      }
   }

   String dbDriverName;
   File targetsDir;
   File processDir;
   File remoteAppDir;
   //TreeMap<Integer,Feed> feeds;
   //TreeMap<Integer,Term> terms;
   //TreeMap<URI,Term> termsByUri;
   Journal journal;
   Logger log;
   boolean connected;
   Properties properties;

   public static Map<String,DB> getDatabases(Logger log,File dir)
      throws ConfigurationException
   {
      Map<String,DB> dbList = new HashMap<String,DB>();
      File dbConf = new File(dir,"db.conf");
      if (dbConf.exists()) {
         DocumentLoader loader = new SAXDocumentLoader();
         try {
            Document doc =loader.load(dbConf.toURI());
            Iterator<Element> databases = doc.getDocumentElement().getElementsByName(AdminXML.NM_DATABASE);
            while (databases.hasNext()) {
               Element databaseE = databases.next();
               URI href = databaseE.getBaseURI().resolve(databaseE.getAttributeValue("href"));
               if (!href.getScheme().equals("file")) {
                  throw new ConfigurationException("The scheme '"+href.getScheme()+"' is not support for database uri "+href);
               }
               DB db = new DB(log,new File(href.getSchemeSpecificPart()));
               Properties properties = db.getConfiguration();
               properties.setProperty("base-uri", databaseE.getBaseURI().toString());
               for (Attribute att : databaseE.getAttributes().values()) {
                  properties.put(att.getName().toString(),att.getText());
               }
               dbList.put(db.getName(),db);
            }
         } catch (IOException ex) {
            throw new ConfigurationException("Cannot load database configuration due to I/O error.",ex);
         } catch (XMLException ex) {
            throw new ConfigurationException("Cannot load database configuration due to XML error.",ex);
         }
      }
      return dbList;
   }
   
   public static void writeList(File dir, Map<String,DB> dbList)
      throws IOException,XMLException
   {
      File dbConf = new File(dir,"db.conf");
      Document doc = InfosetFactory.getDefaultInfoset().createItemConstructor().createDocument();
      Element top = doc.createDocumentElement(AdminXML.NM_DATABASES);
      top.addCharacters("\n");
      for (DB db : dbList.values()) {
         Element database = top.addElement(AdminXML.NM_DATABASE);
         File compDir = new File(dir,db.getName());
         if (compDir.getCanonicalPath().equals(db.getDatabaseDir().getCanonicalPath())) {
            db.getDatabaseDir();
            database.setAttributeValue("href",db.getName());
         } else {
            database.setAttributeValue("href",db.getDatabaseDir().toURI().toString());
         }
         top.addCharacters("\n");
      }

      FileWriter writer = new FileWriter(dbConf);
      XMLWriter.writeDocument(doc,writer);
      writer.close();
      
   }
   
   public static DB createDB(Logger log,File dir,String name)
   {
      DB db = new DB(log,new File(dir,name));
      return db;
   }
   
   /** Creates a new instance of DB */
   public DB(Logger log,File dir) 
   {
      super(dir.getName(),dir);
      this.connected = false;
      this.log = log;
      dbDriverName = getProperty(DBDriver_Prop);
      
      File syncDir = new File(dbDir,"sync");
      targetsDir = new File(syncDir,"targets");
      processDir = new File(syncDir,"process");
      remoteAppDir = new File(dbDir,"remotes");
      /*
      feeds = new TreeMap<Integer,Feed>();
      terms = new TreeMap<Integer,Term>();
      termsByUri = new TreeMap<URI,Term>();
       */
      journal = new Journal(this);
      properties = new Properties();
   }
   
   public Properties getConfiguration() {
      return properties;
   }
   
   public void setConfiguration(Properties value)
   {
      this.properties = value;
   }
   
   public boolean isConnected() {
      return connected;
   }
   
   public File getSyncTargetsDir() {
      return targetsDir;
   }
   
   public File getSyncProcessDir() {
      return processDir;
   }
   
   public File getRemoteAppDir() {
      return remoteAppDir;
   }
   
   public Logger getLogger() {
      return log;
   }
   
   public void connect()
      throws SQLException 
   {
      if (!connected) {
         connected = true;
         bootstrap(true);
         initPool();
      }
   }
   
   public void bootstrap(boolean create)
      throws SQLException 
   {
      try {
         Class.forName(dbDriverName).newInstance();
         
         if (create) {
            checkSchemata();
            File logsDir = new File(dbDir.getParentFile(),"logs");
            if (!logsDir.exists()) {
               logsDir.mkdir();
            }
         }
                  
         String logDir = System.getProperty("atomojo.log.dir");
         if (logDir==null) {
            File ldir = new File(dbDir.getParentFile(),"logs");
            if (ldir.exists()) {
               //System.out.println("atomojo.log.dir="+ldir.getAbsolutePath());
               System.setProperty("atomojo.log.dir", ldir.getAbsolutePath());
            } else {
               //System.out.println("atomojo.log.dir=.");
               System.setProperty("atomojo.log.dir",".");
            }
         }
   
         
         if (!targetsDir.exists()) {
            targetsDir.mkdirs();
         }
         if (!processDir.exists()) {
            processDir.mkdirs();
         }
         if (!remoteAppDir.exists()) {
            remoteAppDir.mkdirs();
         }
         
      } catch (Exception ex) {
         ex.printStackTrace();
         throw new SQLException(ex.getMessage());
      }
      
      
   }
   
   protected void checkSchemata() 
      throws IOException,SQLException,AuthException
   {
      Connection connection = getNonPooled();
      ResultSet dbSchemas = null;
      boolean found = false;

      try {
         
         dbSchemas = connection.getMetaData().getSchemas();
         while (!found && dbSchemas.next()) {
            String name = dbSchemas.getString(1).toLowerCase();
            if (name.equals("atom")) {
               found = true;
            }
         }
         dbSchemas.close();
         dbSchemas = null;
         
         if (!found) {
            log.info("New database detected; loading schemata into database...");
            loadSchema();
         }
      } finally {
         if (dbSchemas!=null) {
            dbSchemas.close();
         }
         connection.close();
      }
   }
   
   public void stop() 
      throws Exception
   {
      close();
   }
   
   public void copyResource(String resourcePath,File outFile)
      throws IOException
   {
      InputStream in = getClass().getResourceAsStream(resourcePath);
      if (in==null) {
         throw new IOException("Cannot open resource "+resourcePath);
      }
      FileOutputStream out = new FileOutputStream(outFile);
      byte [] buffer = new byte[8192];
      int len;
      while ((len=in.read(buffer))>0) {
         out.write(buffer,0,len);
      }
      out.close();
      in.close();
   }
   
   protected void loadSchema()
      throws java.io.IOException,java.sql.SQLException,AuthException
   {
      URL sqlLocation = this.getClass().getResource("atomdb.sql");
      InputStream is = sqlLocation.openStream();
      Reader r = new InputStreamReader(is,"UTF-8");
      StringBuilder sql = new StringBuilder();
      char [] buffer = new char[8192];
      int len = 0;
      while ((len=r.read(buffer))>0) {
         sql.append(buffer,0,len);
      }
      is.close();
      String [] statements = sql.toString().split(";");
      Connection connection = getNonPooled();
      try {
         for (int i=0; i<statements.length; i++) {
            statements[i] = statements[i].trim();
            if (statements[i].length()==0) {
               continue;
            }
            //System.err.println(statements[i]);
            Statement s = connection.createStatement();
            s.execute(statements[i]);
            s.close();
         }
         // Create admin user and group and assign admin user to group
         try {
            Statement s = connection.createStatement();
            s.executeUpdate("insert into atom.groups (name) values ('"+AuthService.ADMIN_GROUP+"')");
            s.close();
            s = connection.createStatement();
            UUID id = UUID.randomUUID();
            String md5Password = User.md5Password(AuthService.ADMIN_USER);
            Timestamp tstamp = new Timestamp((new Date()).getTime());
            s.executeUpdate("insert into atom.author (alias,uuid,name,email,password_md5,created,valid) values ('"+AuthService.ADMIN_USER+"','"+id+"','administrator',null,'"+md5Password+"',{ts '"+tstamp.toString()+"'},'t')");
            s.close();
            int userId = lookupInternalId(STATEMENT_AUTHOR_BY_ALIAS,AuthService.ADMIN_USER);
            if (userId<0) {
               throw new SQLException("User "+AuthService.ADMIN_USER+" cannot be found.");
            }
            int groupId = lookupInternalId(STATEMENT_GROUP_BY_NAME,AuthService.ADMIN_GROUP);
            if (groupId<0) {
               throw new SQLException("Group "+AuthService.ADMIN_GROUP+" cannot be found.");
            }
            s = connection.createStatement();
            int count = s.executeUpdate("insert into atom.group_members (author_ref,group_ref) values ("+userId+","+groupId+")");
            if (count!=1) {
               throw new SQLException("Cannot assign alias "+AuthService.ADMIN_USER+" to group "+AuthService.ADMIN_GROUP+".");
            }
         } catch (NoSuchAlgorithmException ex) {
            throw new SQLException("Cannot create admin user's password: "+ex.getMessage());
         }
      } finally {
         connection.close();
      }
   }
   
   public Journal getJournal() {
      return journal;
   }

   protected int lookupInternalId(int statement,String value)
      throws SQLException
   {
      PreparedStatement s = null;
      ResultSet r = null;
      DBConnection dbConnection = getConnection();
      try {
         s = dbConnection.getStatement(statement);
         s.setString(1,value);
         r = s.executeQuery();
         if (r.next()) {
            return r.getInt(1);
         }
         return -1;
      } finally {
         release(r);
         release(dbConnection);
         release(s);
      }
      
   }
   
   public void release(ResultSet r) 
      throws SQLException
   {
      if (r!=null) {
         r.close();
      }
   }
   
   public void release(PreparedStatement statement)
      throws SQLException
   {
      if (statement!=null) {
         statement.clearParameters();
      }
   }
   
   static final int STATEMENT_ALL_FEEDS                  = 0;
   static final int STATEMENT_ALL_FEEDS_SINCE            = 1;
   static final int STATEMENT_ROOT_FEEDS                 = 2;
   static final int STATEMENT_ALL_ENTRIES_SINCE          = 3;
   static final int STATEMENT_ALL_FEED_ENTRIES_SINCE     = 4;
   static final int STATEMENT_ROOT_FEED                  = 5;
   static final int STATEMENT_ALL_FEED_CHILDREN          = 6;
   static final int STATEMENT_DELETE_FEED_CATEGORIZATION     = 7;
   static final int STATEMENT_DELETE_ENTRY_RESOURCES_BY_FEED = 8;
   static final int STATEMENT_DELETE_FEED_JOURNAL_ENTRIES    = 9;
   static final int STATEMENT_DELETE_FEED_ENTRIES            = 10;
   static final int STATEMENT_DELETE_FEED                    = 11;
   static final int STATEMENT_FEED_BY_UUID                   = 12;
   static final int STATEMENT_FEED_BY_ID                     = 13;
   static final int STATEMENT_ENTRY_BY_ID                    = 14;
   static final int STATEMENT_FEED_BY_TERM                   = 15;
   static final int STATEMENT_FEED_BY_TERM_VALUE             = 16;
   static final int STATEMENT_ENTRY_BY_TERM                  = 17;
   static final int STATEMENT_ENTRY_BY_TERM_VALUE            = 18;
   static final int STATEMENT_DELETE_ENTRY_CATEGORIZATION    = 19;
   static final int STATEMENT_DELETE_ENTRY                   = 20;
   static final int STATEMENT_ALL_ENTRY_RESOURCES            = 21;
   static final int STATEMENT_RESOURCE_BY_ID                 = 22;
   static final int STATEMENT_DELETE_RESOURCE                = 23;
   static final int STATEMENT_ALL_TERMS                      = 24;
   static final int STATEMENT_TERM_BY_ID                     = 25;
   static final int STATEMENT_TERM_BY_URI                    = 26;
   static final int STATEMENT_CREATE_TERM                    = 27;
   static final int STATEMENT_LAST_ID_FROM_TERMS             = 28;
   static final int STATEMENT_FEED_UPDATED                   = 29;
   static final int STATEMENT_CREATE_FEED                    = 30;
   static final int STATEMENT_LAST_ID_FROM_FEEDS             = 31;
   static final int STATEMENT_FEED_BY_NAME                   = 32;
   static final int STATEMENT_FEED_ENTRIES                   = 33;
   static final int STATEMENT_CREATE_ENTRY                   = 34;
   static final int STATEMENT_LAST_ID_FROM_ENTRIES           = 35;
   static final int STATEMENT_RESOURCE_BY_NAME               = 36;
   static final int STATEMENT_ENTRY_BY_UUID                  = 37;
   static final int STATEMENT_CATEGORIZE_FEED                = 38;
   static final int STATEMENT_UNCATEGORIZE_FEED              = 39;
   static final int STATEMENT_FEED_CATEGORIZATION            = 40;
   static final int STATEMENT_FEED_TERM_INSTANCE             = 41;
   static final int STATEMENT_ENTRY_UPDATED                  = 42;
   static final int STATEMENT_CATEGORIZE_ENTRY               = 43;
   static final int STATEMENT_UNCATEGORIZE_ENTRY             = 44;
   static final int STATEMENT_CREATE_RESOURCE                = 45;
   static final int STATEMENT_LAST_ID_FROM_RESOURCES         = 46;
   static final int STATEMENT_ENTRY_CATEGORIZATION           = 47;
   static final int STATEMENT_ENTRY_TERM_INSTANCE            = 48;
   static final int STATEMENT_RESOURCE_UPDATED               = 49;
   static final int STATEMENT_TRUNCATE_DELETE_JOURNAL        = 50;
   static final int STATEMENT_TRUNCATE_UPDATE_JOURNAL        = 51;
   static final int STATEMENT_DELETE_FEED_DELETE_JOURNAL     = 52;
   static final int STATEMENT_DELETE_FEED_UPDATE_JOURNAL     = 53;
   static final int STATEMENT_CREATE_FEED_DELETE_JOURNAL     = 54;
   static final int STATEMENT_CREATE_ENTRY_DELETE_JOURNAL    = 55;
   static final int STATEMENT_DELETE_ENTRY_UPDATE_JOURNAL    = 56;
   static final int STATEMENT_DELETE_MODIFY_FEED_UPDATE_JOURNAL     = 57;
   static final int STATEMENT_DELETE_MODIFY_ENTRY_UPDATE_JOURNAL    = 58;
   static final int STATEMENT_DELETE_MODIFY_RESOURCE_UPDATE_JOURNAL = 59;
   static final int STATEMENT_CREATE_MODIFY_FEED_UPDATE_JOURNAL     = 60;
   static final int STATEMENT_CREATE_MODIFY_ENTRY_UPDATE_JOURNAL    = 61;
   static final int STATEMENT_CREATE_MODIFY_RESOURCE_UPDATE_JOURNAL = 62;
   static final int STATEMENT_UPDATES_BETWEEN                       = 63;
   static final int STATEMENT_UPDATES_BEFORE                        = 64;
   static final int STATEMENT_DELETES_BETWEEN                       = 65;
   static final int STATEMENT_DELETES_BEFORE                        = 66;
   static final int STATEMENT_AUTHOR_BY_ALIAS                       = 67;
   static final int STATEMENT_GROUP_BY_NAME                         = 68;
   static final int STATEMENT_FEED_HAS_TERM_INSTANCE                = 69;
   static final int STATEMENT_ENTRY_HAS_TERM_INSTANCE               = 70;
   static final int STATEMENT_ALL_FEED_ENTRIES                      = 71;
   static final int STATEMENT_RESOURCE_SET_MEDIA_TYPE               = 72;
   static final int STATEMENT_RESOURCE_SET_NAME                     = 73;
   static final int STATEMENT_FEED_SYNCHRONIZED                     = 74;
   static final int STATEMENT_ALL_ENTRIES                           = 75;
   static final int STATEMENT_ALL_ENTRIES_BEFORE                    = 76;
   static final int STATEMENT_ALL_FEED_ENTRIES_BEFORE               = 77;
   
   
   protected void prepare(DBConnection dbConnection)
      throws SQLException
   {
      dbConnection.addStatement(STATEMENT_ALL_FEEDS, "select id from atom.feeds");
      dbConnection.addStatement(STATEMENT_ALL_FEEDS_SINCE,"select id from atom.feeds where modified>=?");
      dbConnection.addStatement(STATEMENT_ROOT_FEEDS,"select id from atom.feeds where parent is null");
      dbConnection.addStatement(STATEMENT_ALL_ENTRIES,"select id from atom.entries");
      dbConnection.addStatement(STATEMENT_ALL_ENTRIES_SINCE,"select id from atom.entries where modified>=?");
      dbConnection.addStatement(STATEMENT_ALL_FEED_ENTRIES_SINCE,"select id from atom.entries where feed=? and modified>=?");
      dbConnection.addStatement(STATEMENT_ROOT_FEED,"select id,uuid,created,modified,synchronized from atom.feeds where name='' and parent is null");
      dbConnection.addStatement(STATEMENT_ALL_FEED_CHILDREN,"select id,name from atom.feeds where parent=?");
      dbConnection.addStatement(STATEMENT_DELETE_FEED_CATEGORIZATION,"delete from atom.categorization where feed=?");
      dbConnection.addStatement(STATEMENT_DELETE_ENTRY_RESOURCES_BY_FEED, "delete from atom.resources where feed=?");
      dbConnection.addStatement(STATEMENT_DELETE_FEED_JOURNAL_ENTRIES,"delete from atom.update_journal where feed=?");
      dbConnection.addStatement(STATEMENT_DELETE_FEED_ENTRIES,"delete from atom.entries where feed=?");
      dbConnection.addStatement(STATEMENT_DELETE_FEED,"delete from atom.feeds where id=?");
      dbConnection.addStatement(STATEMENT_FEED_BY_UUID,"select id,name,parent,created,modified,synchronized from atom.feeds where uuid=?");
      dbConnection.addStatement(STATEMENT_FEED_BY_ID,"select name,uuid,parent,created,modified,synchronized from atom.feeds where id=?");
      dbConnection.addStatement(STATEMENT_ENTRY_BY_ID,"select feed,uuid,created,modified from atom.entries where id=?");
      dbConnection.addStatement(STATEMENT_FEED_BY_TERM,"select id,feed,value from atom.categorization where term=? and entry is null");
      dbConnection.addStatement(STATEMENT_FEED_BY_TERM_VALUE,"select id,feed,value from atom.categorization where term=? and entry is null and value=?");
      dbConnection.addStatement(STATEMENT_ENTRY_BY_TERM,"select id,entry,value from atom.categorization where term=? and entry is not null");
      dbConnection.addStatement(STATEMENT_ENTRY_BY_TERM_VALUE,"select id,entry,value from atom.categorization where term=? and entry is not null and value=?");
      dbConnection.addStatement(STATEMENT_DELETE_ENTRY_CATEGORIZATION,"delete from atom.categorization where entry=?");
      dbConnection.addStatement(STATEMENT_DELETE_ENTRY,"delete from atom.entries where id=?");
      dbConnection.addStatement(STATEMENT_ALL_ENTRY_RESOURCES,"select id,name,type,created,modified from atom.resources where entry=?");
      dbConnection.addStatement(STATEMENT_RESOURCE_BY_ID,"select feed,entry,name,type,created,modified from atom.resources where id=?");
      dbConnection.addStatement(STATEMENT_DELETE_RESOURCE,"delete from atom.resources where id=?");
      dbConnection.addStatement(STATEMENT_ALL_TERMS,"select id,uri from atom.terms");
      dbConnection.addStatement(STATEMENT_TERM_BY_ID,"select uri from atom.terms where id=?");
      dbConnection.addStatement(STATEMENT_TERM_BY_URI,"select id from atom.terms where uri=?");
      dbConnection.addStatement(STATEMENT_CREATE_TERM,"insert into atom.terms (uri) values (?)");
      dbConnection.addStatement(STATEMENT_LAST_ID_FROM_TERMS,"select identity_val_local() from atom.terms");
      dbConnection.addStatement(STATEMENT_FEED_UPDATED,"update atom.feeds set modified=? where id=?");
      dbConnection.addStatement(STATEMENT_FEED_SYNCHRONIZED,"update atom.feeds set synchronized=? where id=?");
      dbConnection.addStatement(STATEMENT_CREATE_FEED,"insert into atom.feeds (name,parent,uuid,created,modified,synchronized) values (?,?,?,?,?,?)");
      dbConnection.addStatement(STATEMENT_LAST_ID_FROM_FEEDS,"select identity_val_local() from atom.feeds");
      dbConnection.addStatement(STATEMENT_FEED_BY_NAME,"select id,uuid,created,modified,synchronized from atom.feeds where name=? and parent=?");
      dbConnection.addStatement(STATEMENT_ALL_FEED_ENTRIES,"select id from atom.entries where feed=? order by modified desc");
      //dbConnection.addStatement(STATEMENT_FEED_ENTRIES,"select id from (select row_number() over (order by modified desc) as rownum , id from atom.entries where feed=? ) as tr where rownum>=? and rownum<?");
      dbConnection.addStatement(STATEMENT_CREATE_ENTRY,"insert into atom.entries (feed,uuid,created,modified) values (?,?,?,?)");
      dbConnection.addStatement(STATEMENT_LAST_ID_FROM_ENTRIES,"select identity_val_local() from atom.entries");
      dbConnection.addStatement(STATEMENT_RESOURCE_BY_NAME,"select id,entry,type,created,modified from atom.resources where name=? and feed=?");
      dbConnection.addStatement(STATEMENT_ENTRY_BY_UUID,"select id,created,modified from atom.entries where uuid=? and feed=?");
      dbConnection.addStatement(STATEMENT_CATEGORIZE_FEED,"insert into atom.categorization (feed,term,value) values (?,?,?)");
      dbConnection.addStatement(STATEMENT_UNCATEGORIZE_FEED,"delete from atom.categorization where feed=? and term=? and entry is null");
      dbConnection.addStatement(STATEMENT_FEED_CATEGORIZATION,"select id,term,value from atom.categorization where feed=? and entry is null");
      dbConnection.addStatement(STATEMENT_FEED_TERM_INSTANCE,"select id,value from atom.categorization where feed=? and entry is null and term=?");
      dbConnection.addStatement(STATEMENT_ENTRY_UPDATED,"update atom.entries set modified=? where id=?");
      dbConnection.addStatement(STATEMENT_CATEGORIZE_ENTRY,"insert into atom.categorization (feed,entry,term,value) values (?,?,?,?)");
      dbConnection.addStatement(STATEMENT_UNCATEGORIZE_ENTRY,"delete from atom.categorization where entry=? and term=?");
      dbConnection.addStatement(STATEMENT_CREATE_RESOURCE,"insert into atom.resources (feed,entry,name,type,created,modified) values (?,?,?,?,?,?)");
      dbConnection.addStatement(STATEMENT_LAST_ID_FROM_RESOURCES,"select identity_val_local() from atom.resources");
      dbConnection.addStatement(STATEMENT_ENTRY_CATEGORIZATION,"select id,term,value from atom.categorization where feed=? and entry=?");
      dbConnection.addStatement(STATEMENT_ENTRY_TERM_INSTANCE,"select id,value from atom.categorization where feed=? and entry=? and term=?");
      dbConnection.addStatement(STATEMENT_RESOURCE_UPDATED, "update atom.resources set modified=? where id=?");
      dbConnection.addStatement(STATEMENT_TRUNCATE_DELETE_JOURNAL,"delete from atom.delete_journal where occurred<?");
      dbConnection.addStatement(STATEMENT_TRUNCATE_UPDATE_JOURNAL,"delete from atom.update_journal where occurred<?");
      dbConnection.addStatement(STATEMENT_DELETE_FEED_DELETE_JOURNAL, "delete from atom.delete_journal where feed=?");
      dbConnection.addStatement(STATEMENT_DELETE_FEED_UPDATE_JOURNAL,"delete from atom.update_journal where feed=?");
      dbConnection.addStatement(STATEMENT_CREATE_FEED_DELETE_JOURNAL,"insert into atom.delete_journal (occurred,feed,path) values (?,?,?)");
      dbConnection.addStatement(STATEMENT_CREATE_ENTRY_DELETE_JOURNAL,"insert into atom.delete_journal (occurred,feed,path,entry) values (?,?,?,?)");
      dbConnection.addStatement(STATEMENT_DELETE_ENTRY_UPDATE_JOURNAL,"delete from atom.update_journal where entry=?");
      dbConnection.addStatement(STATEMENT_DELETE_MODIFY_FEED_UPDATE_JOURNAL,"delete from atom.update_journal where feed=? and operation="+Journal.MODIFY_OPERATION);
      dbConnection.addStatement(STATEMENT_DELETE_MODIFY_ENTRY_UPDATE_JOURNAL,"delete from atom.update_journal where feed=? and entry=? and operation="+Journal.MODIFY_OPERATION);
      dbConnection.addStatement(STATEMENT_DELETE_MODIFY_RESOURCE_UPDATE_JOURNAL,"delete from atom.update_journal where feed=? and entry=? and resource=? and operation="+Journal.MODIFY_OPERATION);
      dbConnection.addStatement(STATEMENT_CREATE_MODIFY_FEED_UPDATE_JOURNAL,"insert into atom.update_journal (occurred,operation,feed) values (?,?,?)");
      dbConnection.addStatement(STATEMENT_CREATE_MODIFY_ENTRY_UPDATE_JOURNAL,"insert into atom.update_journal (occurred,operation,feed,entry) values (?,?,?,?)");
      dbConnection.addStatement(STATEMENT_CREATE_MODIFY_RESOURCE_UPDATE_JOURNAL,"insert into atom.update_journal (occurred,operation,feed,entry,resource) values (?,?,?,?,?)");
      dbConnection.addStatement(STATEMENT_UPDATES_BETWEEN, "select id,occurred,operation,feed,entry,resource from atom.update_journal where occurred>=? and occurred<? order by occurred");
      dbConnection.addStatement(STATEMENT_UPDATES_BEFORE, "select id,occurred,operation,feed,entry,resource from atom.update_journal where occurred<? order by occurred");
      dbConnection.addStatement(STATEMENT_DELETES_BETWEEN, "select id,occurred,feed,path,entry from atom.delete_journal where occurred>=? and occurred<? order by occurred");
      dbConnection.addStatement(STATEMENT_DELETES_BEFORE, "select id,occurred,feed,path,entry from atom.delete_journal where occurred<? order by occurred");
      dbConnection.addStatement(STATEMENT_AUTHOR_BY_ALIAS,"select id from atom.author where alias=?");
      dbConnection.addStatement(STATEMENT_GROUP_BY_NAME,"select id from atom.groups where name=?");
      dbConnection.addStatement(STATEMENT_FEED_HAS_TERM_INSTANCE,"select id from atom.categorization where feed=? and entry is null and term=? and value=?");
      dbConnection.addStatement(STATEMENT_ENTRY_HAS_TERM_INSTANCE,"select id from atom.categorization where feed=? and entry=? and term=? and value=?");
      dbConnection.addStatement(STATEMENT_RESOURCE_SET_MEDIA_TYPE, "update atom.resources set type=? where id=?");
      dbConnection.addStatement(STATEMENT_RESOURCE_SET_NAME, "update atom.resources set name=? where id=?");
      dbConnection.addStatement(STATEMENT_ALL_ENTRIES_BEFORE,"select id from atom.entries where modified<? order by modified desc");
      dbConnection.addStatement(STATEMENT_ALL_FEED_ENTRIES_BEFORE,"select id from atom.entries where feed=? and modified<? order by modified desc");
   }

   Iterator<Feed> iterateFeeds(ResultSet r,DBConnection connection) {
      return new FeedIterator(r,this,connection);
   }
   
   public Iterator<Feed> getFeeds()
      throws SQLException
   {
      DBConnection dbConnection = getConnection();
      try {
         PreparedStatement s = dbConnection.getStatement(STATEMENT_ALL_FEEDS);
         return new FeedIterator(s.executeQuery(),this,dbConnection);
      } catch (SQLException ex) {
         release(dbConnection);
         throw ex;
      }
   }
   
   public Iterator<Feed> getFeedsModifiedSince(Date start)
      throws SQLException
   {
      DBConnection dbConnection = getConnection();
      try {
         Timestamp tstamp = new Timestamp(start.getTime());
         PreparedStatement s = dbConnection.getStatement(STATEMENT_ALL_FEEDS_SINCE);
         s.setTimestamp(1,tstamp);
         return new FeedIterator(s.executeQuery(),this,dbConnection);
      } catch (SQLException ex) {
         release(dbConnection);
         throw ex;
      }
   }
   
   public Iterator<Feed> getRootFeeds()
      throws SQLException
   {
      DBConnection dbConnection = getConnection();
      try {
         PreparedStatement s = dbConnection.getStatement(STATEMENT_ROOT_FEEDS);
         return new FeedIterator(s.executeQuery(),this,dbConnection);
      } catch (SQLException ex) {
         release(dbConnection);
         throw ex;
      }
   }
   
   public Iterator<Entry> getEntriesModifiedSince(Date start)
      throws SQLException
   {
      return getEntriesModifiedSince(null,start);
   }
   public Iterator<Entry> getEntriesModifiedSince(Feed feed,Date start)
      throws SQLException
   {
      DBConnection dbConnection = getConnection();
      try {
         Timestamp tstamp = new Timestamp(start.getTime());
         PreparedStatement statement;
         if (feed==null) {
            statement = dbConnection.getStatement(STATEMENT_ALL_ENTRIES_SINCE ); 
            statement.setTimestamp(1, tstamp);
         } else {
            statement = dbConnection.getStatement(STATEMENT_ALL_FEED_ENTRIES_SINCE); 
            statement.setInt(1,feed.getId());
            statement.setTimestamp(2, tstamp);

         }
         return new DBIterator<Entry>(statement.executeQuery(),new ResultConstructor<Entry>() {
            public Entry newInstance(ResultSet set) 
               throws SQLException
            {
               int id = set.getInt(1);
               // TODO: this makes a double query. Replace with the full select of columns
               return getEntry(id);
            }
         },this,dbConnection);
      } catch (SQLException ex) {
         release(dbConnection);
         throw ex;
      }
   }
   
   public DBIterator<Entry> getEntriesModified(int startRow, int limit)
      throws SQLException
   {
      return getEntriesModifiedBefore(null,new Date(),startRow,limit);
   }
   public DBIterator<Entry> getEntriesModifiedBefore(Date startDate,int startRow, int limit)
      throws SQLException
   {
      return getEntriesModifiedBefore(null,startDate,startRow,limit);
   }
   public DBIterator<Entry> getEntriesModified(Feed feed,int startRow, int limit)
      throws SQLException
   {
      return getEntriesModifiedBefore(feed,new Date(),startRow,limit);
   }
   public DBIterator<Entry> getEntriesModifiedBefore(Feed feed,Date startDate,int startRow, int limit)
      throws SQLException
   {
      DBConnection dbConnection = getConnection();
      try {
         Timestamp tstamp = new Timestamp(startDate.getTime());
         PreparedStatement statement;
         if (feed==null) {
            statement = dbConnection.getStatement(STATEMENT_ALL_ENTRIES_BEFORE );
            statement.setTimestamp(1, tstamp);
         } else {
            statement = dbConnection.getStatement(STATEMENT_ALL_FEED_ENTRIES_BEFORE);
            statement.setInt(1,feed.getId());
            statement.setTimestamp(2, tstamp);
         }
         DBIterator<Entry> result = new DBIterator<Entry>(statement.executeQuery(),new ResultConstructor<Entry>() {
            public Entry newInstance(ResultSet set)
               throws SQLException
            {
               int id = set.getInt(1);
               // TODO: this makes a double query. Replace with the full select of columns
               return getEntry(id);
            }
         },this,dbConnection);
         result.setStart(startRow);
         result.setMaximum(limit);
         return result;
      } catch (SQLException ex) {
         release(dbConnection);
         throw ex;
      }
   }

   public Iterator<Entry> getEntries()
      throws SQLException
   {
      DBConnection dbConnection = getConnection();
      try {
         PreparedStatement statement = dbConnection.getStatement(STATEMENT_ALL_ENTRIES); 
         return new DBIterator<Entry>(statement.executeQuery(),new ResultConstructor<Entry>() {
            public Entry newInstance(ResultSet set) 
               throws SQLException
            {
               int id = set.getInt(1);
               // TODO: this makes a double query. Replace with the full select of columns
               return getEntry(id);
            }
         },this,dbConnection);
      } catch (SQLException ex) {
         release(dbConnection);
         throw ex;
      }
   }
   
   public Feed getRoot()
      throws SQLException
   {
      PreparedStatement s = null;
      ResultSet r = null;
      DBConnection dbConnection = getConnection();
      try {
         s = dbConnection.getStatement(STATEMENT_ROOT_FEED);
         r = s.executeQuery();
         if (r.next()) {
            int id = r.getInt(1);
            String uuidS = r.getString(2);
            Feed f = new Feed(this,id,-1,"",uuidS==null ? null : UUID.fromString(uuidS),r.getTimestamp(3),r.getTimestamp(4),r.getTimestamp(5));
            return f;
         }
         return null;
      } finally {
         release(r);
         release(s);
         release(dbConnection);
      }
   }
   
   public Feed createRoot()
      throws SQLException
   {
      Feed temp = new Feed(this,-1,-1,null,null,null,null,null);
      return temp.createChild("",UUID.randomUUID());
   }
   
   public Feed createRoot(UUID id)
      throws SQLException
   {
      Feed temp = new Feed(this,-1,-1,null,null,null,null,null);
      return temp.createChild("",id);
   }
   
   public void deleteFeed(int topId) 
      throws SQLException
   {
      List<List<Feed>> depthlist = new ArrayList<List<Feed>>();
      List<Feed> current = new ArrayList<Feed>();
      current.add(new Feed(this,topId,-1,null,null,null,null,null));
      depthlist.add(current);
      boolean done = false;
      DBConnection dbConnection = getConnection();
      try {
         do {
            List<Feed> next = new ArrayList<Feed>();
            for (Feed f : current) {
               PreparedStatement s = null;
               ResultSet r = null;
               try {
                  s = dbConnection.getStatement(STATEMENT_ALL_FEED_CHILDREN);
                  s.setInt(1,f.getId());
                  r = s.executeQuery();
                  while (r.next()) {
                     log.fine("Adding id="+r.getInt(1)+", name="+r.getString(2));
                     next.add(new Feed(this,r.getInt(1),f.getId(),r.getString(2),null,null,null,null));
                  }
               } finally {
                  release(r);
                  release(s);
               }
            }
            depthlist.add(next);
            current = next;
            if (next.size()==0) {
               done = true;
            }
         } while(!done);

         Collections.reverse(depthlist);

         for (List<Feed> toDelete : depthlist) {
            for (Feed f : toDelete) {
               PreparedStatement s = null;
               // delete the categorization
               // Note: since categorization contains entry cateogorizations with the feed column
               // set, this delete ensures the entry delete will succeed.
               //log.info("Deleting id="+f.getId()+", name="+f.getName());
               try {
                  s = dbConnection.getStatement(STATEMENT_DELETE_FEED_CATEGORIZATION);
                  s.setInt(1,f.getId());
                  s.executeUpdate();
               } finally {
                  release(s);
               }
               // delete the entry resources
               try {
                  s = dbConnection.getStatement(STATEMENT_DELETE_ENTRY_RESOURCES_BY_FEED);
                  s.setInt(1,f.getId());
                  s.executeUpdate();
               } finally {
                  release(s);
               }
               // delete the journal entries
               try {
                  s = dbConnection.getStatement(STATEMENT_DELETE_FEED_JOURNAL_ENTRIES);
                  s.setInt(1,f.getId());
                  s.executeUpdate();
               } finally {
                  release(s);
               }
               // delete the entries
               try {
                  s = dbConnection.getStatement(STATEMENT_DELETE_FEED_ENTRIES);
                  s.setInt(1,f.getId());
                  s.executeUpdate();
               } finally {
                  release(s);
               }
               
               // Delete the feed
               try {
                  s = dbConnection.getStatement(STATEMENT_DELETE_FEED);
                  s.setInt(1,f.getId());
                  s.executeUpdate();
               } finally {
                  release(s);
               }
            }
         }
      } finally {
         release(dbConnection);
      }
      
   }
   
   public Feed findFeed(UUID uuid) 
      throws SQLException
   {
      PreparedStatement s = null;
      ResultSet r = null;
      DBConnection dbConnection = getConnection();
      try {
         s = dbConnection.getStatement(STATEMENT_FEED_BY_UUID);
         s.setString(1,uuid.toString());
         r = s.executeQuery();
         if (r.next()) {
            int id = r.getInt(1);
            int parent = r.getInt(3);
            if (parent==0) {
               parent = -1;
            }
            Feed f = new Feed(this,id,parent,r.getString(2),uuid,r.getTimestamp(4),r.getTimestamp(5),r.getTimestamp(6));
            return f;
         } else {
            return null;
         }
      } finally {
         release(r);
         release(s);
         release(dbConnection);
      }
   }
   
   public Feed getFeed(int id)
      throws SQLException
   {
      Feed f = null;
      PreparedStatement s = null;
      ResultSet r = null;
      DBConnection dbConnection = getConnection();
      try {
         s = dbConnection.getStatement(STATEMENT_FEED_BY_ID);
         s.setInt(1,id);
         r = s.executeQuery();
         if (r.next()) {
            int parent = r.getInt(3);
            if (parent==0) {
               parent = -1;
            }
            f = new Feed(this,id,parent,r.getString(1),UUID.fromString(r.getString(2)),r.getTimestamp(4),r.getTimestamp(5),r.getTimestamp(6));
         }
      } finally {
         release(r);
         release(s);
         release(dbConnection);
      }
      return f;
   }
   
   public Feed findFeedByPath(String [] segments)
      throws SQLException
   {
      return findFeedByPath(segments,0,segments.length);
   }
   public Feed findFeedByPath(String [] segments,int start,int length)
      throws SQLException
   {
      Feed root = getRoot();
      if (length==0 || length==1 && segments[0].length()==0) {
         return root;
      }
      int end = start+length;
      Feed parent = root;

      for (int i=start; parent!=null && i<end; i++) {
         parent = parent.getChild(segments[i]);
      }
      return parent;
   }
   
   public Entry getEntry(int id)
      throws SQLException
   {
      Entry e = null;
      PreparedStatement s = null;
      ResultSet r = null;
      DBConnection dbConnection = getConnection();
      try {
         s = dbConnection.getStatement(STATEMENT_ENTRY_BY_ID);
         s.setInt(1, id);
         r = s.executeQuery();
         if (r.next()) {
            e = new Entry(this,id,r.getInt(1),UUID.fromString(r.getString(2)),r.getTimestamp(3),r.getTimestamp(4));
         }
      } finally {
         release(r);
         release(s);
         release(dbConnection);
      }
      return e;
   }
   
   public Iterator<TermInstance<Feed>> getFeedsByTerm(Term term)
      throws SQLException
   {
      return getFeedsByTerm(term,null);
   }
   public Iterator<TermInstance<Feed>> getFeedsByTerm(final Term term,Object value)
      throws SQLException
   {
      DBConnection dbConnection = getConnection();
      try {
         PreparedStatement s = null;
         if (value==null || value==Nil.getInstance()) {
            s = dbConnection.getStatement(STATEMENT_FEED_BY_TERM);
            s.setInt(1, term.getId());
         } else {
            s = dbConnection.getStatement(STATEMENT_FEED_BY_TERM_VALUE);
            s.setInt(1, term.getId());
            s.setString(2, value.toString());
         }
         ResultSet r = s.executeQuery();
         return new DBIterator<TermInstance<Feed>>(r,new ResultConstructor<TermInstance<Feed>>() {
            public TermInstance<Feed> newInstance(ResultSet set) 
               throws SQLException
            {
               int id = set.getInt(1);
               int feedId = set.getInt(2);
               String value = set.getString(3);
               Feed feed = getFeed(feedId);
               return new TermInstance<Feed>(id,term,feed,value);
            }
         },this,dbConnection);
      } catch (SQLException ex) {
         release(dbConnection);
         throw ex;
      }
   }
   
   public Iterator<TermInstance<Entry>> getEntriesByTerm(Term term)
      throws SQLException
   {
      return getEntriesByTerm(term,null);
   }
   
   public Iterator<TermInstance<Entry>> getEntriesByTerm(final Term term,Object value)
      throws SQLException
   {
      DBConnection dbConnection = getConnection();
      try {
         PreparedStatement s = null;
         if (value==null || value==Nil.getInstance()) {
            s = dbConnection.getStatement(STATEMENT_ENTRY_BY_TERM);
            s.setInt(1, term.getId());
         } else {
            s = dbConnection.getStatement(STATEMENT_ENTRY_BY_TERM_VALUE);
            s.setInt(1, term.getId());
            s.setString(2, value.toString());
         }
         ResultSet r = s.executeQuery();
         return new DBIterator<TermInstance<Entry>>(r,new ResultConstructor<TermInstance<Entry>>() {
            public TermInstance<Entry> newInstance(ResultSet set) 
               throws SQLException
            {
               int id = set.getInt(1);
               int entryId = set.getInt(2);
               String value = set.getString(3);
               Entry entry = getEntry(entryId);
               return new TermInstance<Entry>(id,term,entry,value);
            }
         },this,dbConnection);
      } catch (SQLException ex) {
         release(dbConnection);
         throw ex;
      }
   }
   
   public void deleteEntry(int feedId, int entryId,MediaEntryListener listener)
      throws SQLException
   {
      for (Iterator<EntryMedia> resources = getResources(feedId,entryId);
           resources.hasNext(); ) {
         EntryMedia resource = resources.next();
         resource.delete();
         if (listener!=null) {
            listener.onDelete(resource);
         }
      }
      DBConnection dbConnection = getConnection();
      try {
         PreparedStatement s = null;
         try {
            s = dbConnection.getStatement(STATEMENT_DELETE_ENTRY_CATEGORIZATION);
            s.setInt(1, entryId);
            s.executeUpdate();
         } finally {
            release(s);
         }
         try {
            s = dbConnection.getStatement(STATEMENT_DELETE_ENTRY);
            s.setInt(1, entryId);
            s.executeUpdate();
         } finally {
            release(s);
         }
      } finally {
         release(dbConnection);
      }
   }
   
   public Iterator<EntryMedia> getResources(final int feedId, final int entryId)
      throws SQLException
   {
      boolean ok = true;
      DBConnection dbConnection = getConnection();
      try {
         PreparedStatement s = dbConnection.getStatement(STATEMENT_ALL_ENTRY_RESOURCES);
         s.setInt(1,entryId);
         ResultSet r = s.executeQuery();
         return new DBIterator<EntryMedia>(r,new ResultConstructor<EntryMedia>() {
            public EntryMedia newInstance(ResultSet set) 
               throws SQLException
            {
               return new EntryMedia(DB.this,set.getInt(1),feedId,entryId,set.getString(2),MediaType.valueOf(set.getString(3)),set.getTimestamp(4),set.getTimestamp(5));
            }
         },this,dbConnection);
      } catch (SQLException ex) {
         release(dbConnection);
         throw ex;
      }
   }
   
   public EntryMedia getResource(int resourceId)
      throws SQLException
   {
      DBConnection dbConnection = getConnection();
      PreparedStatement s = null;
      ResultSet r = null;
      try {
         s = dbConnection.getStatement(STATEMENT_RESOURCE_BY_ID);
         s.setInt(1,resourceId);
         r = s.executeQuery();
         if (r.next()) {
            return new EntryMedia(DB.this,resourceId,r.getInt(1),r.getInt(2),r.getString(3),MediaType.valueOf(r.getString(4)),r.getTimestamp(5),r.getTimestamp(6));
         }
      } finally {
         release(r);
         release(s);
         release(dbConnection);
      }
      return null;
   }
   
   public void deleteResource(int id)
      throws SQLException
   {
      DBConnection dbConnection = getConnection();
      PreparedStatement s = null;
      try {
         s = dbConnection.getStatement(STATEMENT_DELETE_RESOURCE);
         s.setInt(1,id);
         s.executeUpdate();
      } finally {
         release(s);
         release(dbConnection);
      }
   }
   
   
   public String getDriverName() {
      return dbDriverName;
   }
   
   public void setDriverName(String dbDriverName) {
      this.dbDriverName = dbDriverName;
   }
   
   public Iterator<Term> getTerms()
      throws SQLException
   {
      DBConnection dbConnection = getConnection();
      try {
         PreparedStatement s = dbConnection.getStatement(STATEMENT_ALL_TERMS);
         return new DBIterator<Term>(s.executeQuery(),new ResultConstructor<Term>() {
            public Term newInstance(ResultSet set) 
               throws SQLException
            {
               int id = set.getInt(1);
               String uri = set.getString(2);
               Term t = new Term(DB.this,id,URI.create(uri));
               return t;
            }
         },this,dbConnection);
      } catch (SQLException ex) {
         release(dbConnection);
         throw ex;
      }
   }
   
   public Term getTerm(int id)
      throws SQLException
   {
      Term t = null;
      PreparedStatement s = null;
      ResultSet r = null;
      DBConnection dbConnection = getConnection();
      try {
         s = dbConnection.getStatement(STATEMENT_TERM_BY_ID);
         s.setInt(1,id);
         r = s.executeQuery();
         if (r.next()) {
            t = new Term(this,id,URI.create(r.getString(1)));
         }
      } finally {
         release(r);
         release(s);
         release(dbConnection);
      }
      return t;
   }
   
   public Term findTerm(URI termUri) 
      throws SQLException
   {
      URI u = termUri.normalize();
      Term t = null;
      PreparedStatement s = null;
      ResultSet r = null;
      DBConnection dbConnection = getConnection();
      try {
         s = dbConnection.getStatement(STATEMENT_TERM_BY_URI);
         s.setString(1,u.toString());
         r = s.executeQuery();
         if (r.next()) {
            // TODO: should get the whole term to get rid of dual queries
            t = getTerm(r.getInt(1));
         }
      } finally {
         release(r);
         release(s);
         release(dbConnection);
      }
      return t;
   }
   
   public Term createTerm(URI termUri) 
      throws SQLException
   {
      URI u = termUri.normalize();
      Term t = findTerm(u);
      if (t==null) {
         DBConnection dbConnection = getConnection();
         PreparedStatement s = null;
         ResultSet r = null;
         try {
            s = dbConnection.getStatement(STATEMENT_CREATE_TERM);
            s.setString(1, u.toString());
            int count = s.executeUpdate();
            if (count>0) {
               release(s);
               s = dbConnection.getStatement(STATEMENT_LAST_ID_FROM_TERMS);
               r = s.executeQuery();
               if (r.next()) {
                  int id = r.getInt(1);
                  t = new Term(this,id,u);
               }
            }
         } finally {
            release(r);
            release(s);
            release(dbConnection);
         }
      }
      return t;
      
   }
   
   public Iterator<SyncTarget> getSyncTargets()
      throws XMLException
   {
      File [] files = targetsDir.listFiles();
      List<SyncTarget> targets = new ArrayList<SyncTarget>();
      for (int i=0; i<files.length; i++) {
         targets.add(new SyncTarget(this,files[i].getName()));
      }
      return targets.iterator();
   }
   
   public SyncTarget getSyncTarget(String name)
   {
      SyncTarget app = new SyncTarget(this,name);
      if (app.exists()) {
         return app;
      } else {
         return null;
      }
   }

   public Iterator<SyncProcess> getSyncProcesses()
      throws XMLException
   {
      File [] files = processDir.listFiles();
      List<SyncProcess> procs = new ArrayList<SyncProcess>();
      for (int i=0; i<files.length; i++) {
         procs.add(new SyncProcess(this,files[i].getName()));
      }
      return procs.iterator();
   }
   
   
   public SyncProcess getSyncProcess(String name)
   {
      SyncProcess proc = new SyncProcess(this,name);
      if (proc.exists()) {
         return proc;
      } else {
         return null;
      }
   }


   public Iterator<RemoteApp> getRemoteApps()
      throws XMLException
   {
      File [] files = remoteAppDir.listFiles();
      List<RemoteApp> apps = new ArrayList<RemoteApp>();
      for (int i=0; i<files.length; i++) {
         apps.add(new RemoteApp(this,files[i].getName()));
      }
      return apps.iterator();
   }
   
   public RemoteApp getRemoteApp(String name)
   {
      RemoteApp app = new RemoteApp(this,name);
      if (app.exists()) {
         return app;
      } else {
         return null;
      }
   }
   
   public interface CollectionLocator {
      String makeHref(Feed feed)
         throws SQLException;
   }
   
   public void getIntrospection(Storage storage,String basePath,CollectionLocator locator,ItemDestination dest)
      throws SQLException,IOException,XMLException
   {
      this.getIntrospection(storage, null, basePath, locator, dest);
   }
   public void getIntrospection(Storage storage,String xmlBase,String basePath,CollectionLocator locator,ItemDestination dest)
      throws SQLException,IOException,XMLException
   {
      Iterator<Feed> feeds = getFeeds();
      ItemConstructor constructor = InfosetFactory.getDefaultInfoset().createItemConstructor();
      dest.send(constructor.createDocument());
      Element service = constructor.createElement(XML.SERVICE);
      service.addNamespaceBinding(Name.NO_PREFIX,AtomResource.APP_NAMESPACE);
      service.addNamespaceBinding("atom",AtomResource.ATOM_NAMESPACE);
      if (xmlBase!=null) {
         service.setBaseURI(URI.create(xmlBase));
         service.setAttributeValue(Attribute.XML_BASE, xmlBase);
      }
      dest.send(service);
      dest.send(constructor.createElement(XML.WORKSPACE));
      dest.send(constructor.createElement(AtomResource.TITLE_NAME));
      dest.send(constructor.createCharacters("Feeds"));
      dest.send(constructor.createElementEnd(AtomResource.TITLE_NAME));
      while (feeds.hasNext()) {
         Feed feed = feeds.next();
         String href = locator.makeHref(feed);
         String title = storage.getFeedTitle(feed.getPath(),feed.getUUID());
         Element collection = constructor.createElement(XML.COLLECTION);
         collection.setAttributeValue("href",basePath+href);
         dest.send(collection);
         dest.send(constructor.createElement(AtomResource.TITLE_NAME));
         if (title!=null) {
            dest.send(constructor.createCharacters(title));
         }
         dest.send(constructor.createElementEnd(AtomResource.TITLE_NAME));
         dest.send(constructor.createElement(XML.ACCEPT));
         dest.send(constructor.createCharacters("application/atom+xml;type=entry"));
         dest.send(constructor.createElementEnd(XML.ACCEPT));
         dest.send(constructor.createElement(XML.ACCEPT));
         dest.send(constructor.createCharacters("*/*"));
         dest.send(constructor.createElementEnd(XML.ACCEPT));
         dest.send(constructor.createElementEnd(XML.COLLECTION));
      }
      dest.send(constructor.createElementEnd(XML.WORKSPACE));
      dest.send(constructor.createElementEnd(XML.SERVICE));
      dest.send(constructor.createDocumentEnd());
   }

}
